﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Waiter.Common;
using System.Data;
using Waiter.DataLayer;

namespace Waiter.Controler
{
    class ReportCtrl
    {
        public List<Dictionary<string, object>> fsGetTotalRevenue(DateTime startDate, DateTime endDate, int displayMode)
        {
            MasterCtrl masterCtrl = new MasterCtrl();
            List<Dictionary<string, object>> resultList = new List<Dictionary<string, object>>();

            // truong hop thong ke theo ban
            if (displayMode == 1)
            {
                List<TABLE> tableList = (from s in CommonGlobalVariable.globalDataContext.TABLEs
                                         where s.DEL_FLG == CommonConstants.FLAG_OFF
                                         select s).ToList();
                if (tableList == null || tableList.Count == 0)
                {
                    return null;
                }

                long total = 0;
                foreach (TABLE tableItem in tableList)
                {
                    total = 0;
                    try
                    {
                        total = (from s in CommonGlobalVariable.globalDataContext.BILLs
                                 where s.ORDER_COUPON.TABLE_CODE == tableItem.CODE
                                     && s.FINAL_BILL == true && s.STATUS_CODE == (int)BillStatus.USE
                                     && s.CREATE_DATE >= startDate && s.CREATE_DATE <= endDate
                                 select s.TOTAL_AMOUNT).Sum();
                    }
                    catch (Exception ex)
                    {
                        CommonLogging.fsWriteLog(CommonConstants.LOG_TYPE_ERROR,
                            "ReportCtrl.fsGetTotalRevenue displayMode == 1",
                            CommonUtils.fsParamListToString(new object[3] { startDate, endDate, displayMode }),
                            ex.ToString());
                    }

                    Dictionary<string, object> resultItem = new Dictionary<string, object>();
                    resultItem.Add("TABLE_NAME", tableItem.TABLE_NAME);
                    resultItem.Add("AREA_NAME", masterCtrl.fsGetMasterFromGroupCode(CommonConstants.CAC_KHU_VUC, tableItem.AREA_CODE).VALUE);
                    resultItem.Add("TOTAL_AMOUNT", total);
                    resultList.Add(resultItem);
                }
            }
            // truong hop thong ke theo khu vuc
            else if (displayMode == 2)
            {
                List<MASTER> areaList = masterCtrl.fsGetMasterFromGroupCode(CommonConstants.CAC_KHU_VUC);
                if (areaList == null || areaList.Count == 0)
                {
                    return null;
                }

                long total = 0;
                foreach (MASTER areaItem in areaList)
                {
                    total = 0;
                    try
                    {
                        total = (from s in CommonGlobalVariable.globalDataContext.BILLs
                                        where s.ORDER_COUPON.TABLE.AREA_CODE == areaItem.KEY_CODE
                                            && s.FINAL_BILL == true && s.STATUS_CODE == (int)BillStatus.USE
                                            && s.CREATE_DATE >= startDate && s.CREATE_DATE <= endDate
                                        select s.TOTAL_AMOUNT).Sum();
                    }
                    catch (Exception ex)
                    {
                        CommonLogging.fsWriteLog(CommonConstants.LOG_TYPE_ERROR,
                            "ReportCtrl.fsGetTotalRevenue displayMode == 2",
                            CommonUtils.fsParamListToString(new object[3] { startDate, endDate, displayMode }),
                            ex.ToString());
                    }
                    
                    Dictionary<string, object> resultItem = new Dictionary<string, object>();
                    resultItem.Add("AREA_NAME", areaItem.VALUE);
                    resultItem.Add("TOTAL_AMOUNT", total);
                    resultList.Add(resultItem);
                }
            }

            return resultList;
        }

        public List<ViewRptSaleNumber> fsGetTotalSaleNumber(DateTime startDate, DateTime endDate, int type, string typeCode, int displayMode)
        {
            MasterCtrl masterCtrl = new MasterCtrl();
            List<ViewRptSaleNumber> resultList = new List<ViewRptSaleNumber>();
            string[] parameter = new string[3] { CommonUtils.fsFormatDate(CommonConstants.YYYYMMDDHHMMSS, startDate), CommonUtils.fsFormatDate(CommonConstants.YYYYMMDDHHMMSS, endDate), typeCode };

            // thong ke theo mon
            StringBuilder sql = new StringBuilder();
            // thong ke theo tat ca ban
            if (type == 1)
            {
                sql.Append(" SELECT MENU.CODE_GROUP AS code, MENU.DRINKS_NAME AS name, ISNULL(SALES_INFO.TOTAL_NUMBER,0) AS total");
                sql.Append(" FROM MENU LEFT JOIN");
                sql.Append(" (");
                sql.Append("    SELECT");
                sql.Append("        ORDER_COUPON_D.MENU_CODE AS CODE,");
                sql.Append("        SUM(ORDER_COUPON_D.COUNT) AS TOTAL_NUMBER");
                sql.Append("    FROM ORDER_COUPON");
                sql.Append("        INNER JOIN BILL ON ORDER_COUPON.CODE = BILL.COUPON_CODE");
                sql.Append("        INNER JOIN ORDER_COUPON_D ON ORDER_COUPON.CODE = ORDER_COUPON_D.COUPON_CODE");
                sql.Append("    WHERE");
                sql.Append("        BILL.STATUS_CODE = 0");
                sql.Append("        AND BILL.FINAL_BILL = 1");
                sql.Append("        AND BILL.CREATE_DATE BETWEEN {0} AND {1}");
                sql.Append("    GROUP BY ORDER_COUPON_D.MENU_CODE");
                sql.Append(" ) AS SALES_INFO");
                sql.Append(" ON MENU.CODE = SALES_INFO.CODE");
                sql.Append(" ORDER BY MENU.DRINKS_NAME");
            }
            // thong ke theo ban
            else if (type == 2)
            {
                sql.Append(" SELECT MENU.CODE_GROUP AS code, MENU.DRINKS_NAME AS name, ISNULL(SALES_INFO.TOTAL_NUMBER,0) AS total");
                sql.Append(" FROM MENU LEFT JOIN");
                sql.Append(" (");
                sql.Append("    SELECT");
                sql.Append("        ORDER_COUPON_D.MENU_CODE AS CODE,");
                sql.Append("        SUM(ORDER_COUPON_D.COUNT) AS TOTAL_NUMBER");
                sql.Append("    FROM ORDER_COUPON");
                sql.Append("        INNER JOIN BILL ON ORDER_COUPON.CODE = BILL.COUPON_CODE");
                sql.Append("        INNER JOIN ORDER_COUPON_D ON ORDER_COUPON.CODE = ORDER_COUPON_D.COUPON_CODE");
                sql.Append("    WHERE");
                sql.Append("        BILL.STATUS_CODE = 0");
                sql.Append("        AND BILL.FINAL_BILL = 1");
                sql.Append("        AND BILL.CREATE_DATE BETWEEN {0} AND {1}");
                sql.Append("        AND ORDER_COUPON.TABLE_CODE = {2}");
                sql.Append("    GROUP BY ORDER_COUPON_D.MENU_CODE");
                sql.Append(" ) AS SALES_INFO");
                sql.Append(" ON MENU.CODE = SALES_INFO.CODE");
                sql.Append(" ORDER BY MENU.DRINKS_NAME");
            }
            // thong ke theo khu vuc
            else if (type == 3)
            {
                sql.Append(" SELECT MENU.CODE_GROUP AS code, MENU.DRINKS_NAME AS name, ISNULL(SALES_INFO.TOTAL_NUMBER,0) AS total");
                sql.Append(" FROM MENU LEFT JOIN");
                sql.Append(" (");
                sql.Append("    SELECT");
                sql.Append("        ORDER_COUPON_D.MENU_CODE AS CODE,");
                sql.Append("        SUM(ORDER_COUPON_D.COUNT) AS TOTAL_NUMBER");
                sql.Append("    FROM ORDER_COUPON");
                sql.Append("        INNER JOIN BILL ON ORDER_COUPON.CODE = BILL.COUPON_CODE");
                sql.Append("        INNER JOIN [TABLE] ON ORDER_COUPON.TABLE_CODE = [TABLE].CODE");
                sql.Append("        INNER JOIN ORDER_COUPON_D ON ORDER_COUPON.CODE = ORDER_COUPON_D.COUPON_CODE");
                sql.Append("    WHERE");
                sql.Append("        BILL.STATUS_CODE = 0");
                sql.Append("        AND BILL.FINAL_BILL = 1");
                sql.Append("        AND BILL.CREATE_DATE BETWEEN {0} AND {1}");
                sql.Append("        AND [TABLE].AREA_CODE = {2}");
                sql.Append("    GROUP BY ORDER_COUPON_D.MENU_CODE");
                sql.Append(" ) AS SALES_INFO");
                sql.Append(" ON MENU.CODE = SALES_INFO.CODE");
                sql.Append(" ORDER BY MENU.DRINKS_NAME");
            }

            try
            {
                resultList = CommonGlobalVariable.globalDataContext.ExecuteQuery<ViewRptSaleNumber>(sql.ToString(), parameter).ToList<ViewRptSaleNumber>();
            }
            catch (Exception ex)
            {
                CommonLogging.fsWriteLog(CommonConstants.LOG_TYPE_ERROR,
                            "ReportCtrl.fsGetTotalSaleNumber",
                            CommonUtils.fsParamListToString(new object[4] { startDate, endDate, type, displayMode }),
                            ex.ToString());
                resultList = new List<ViewRptSaleNumber>();
            }

            // truong hop thong ke theo nhom mon
            if (displayMode == 2)
            {
                try
                {
                    resultList = (from s in resultList
                                  group s by s.code into g
                                  select new ViewRptSaleNumber()
                                  {
                                      code = g.Key,
                                      name = "",
                                      total = g.Sum(q => q.total)
                                  }).ToList();
                }
                catch (Exception ex)
                {
                    CommonLogging.fsWriteLog(CommonConstants.LOG_TYPE_ERROR,
                            "ReportCtrl.fsGetTotalSaleNumber displayMode == 2",
                            CommonUtils.fsParamListToString(new object[4] { startDate, endDate, type, displayMode }),
                            ex.ToString());
                    resultList = new List<ViewRptSaleNumber>();
                }
            }
            return resultList;
        }

        public List<ViewRptBill> fsGetTotalBillList(DateTime startDate, DateTime endDate, int billType)
        {
            List<ViewRptBill> resultList = new List<ViewRptBill>();

            try
            {
                // thong ke tat ca hoa don
                if (billType == 1)
                {
                    resultList = (from s in CommonGlobalVariable.globalDataContext.BILLs
                                  where s.CREATE_DATE >= startDate && s.CREATE_DATE <= endDate
                                  select new ViewRptBill()
                                  {
                                      code = s.CODE,
                                      createDate = s.CREATE_DATE,
                                      staff = s.OPID,
                                      tableName = s.ORDER_COUPON.TABLE.TABLE_NAME,
                                      total = s.TOTAL_AMOUNT,
                                      note = s.NOTE,
                                      status = s.STATUS_CODE
                                  }).ToList<ViewRptBill>();
                }
                // thong ke cac hoa don su dung
                else if (billType == 2)
                {
                    resultList = (from s in CommonGlobalVariable.globalDataContext.BILLs
                                  where s.CREATE_DATE >= startDate && s.CREATE_DATE <= endDate
                                    && s.STATUS_CODE == (int)BillStatus.USE
                                  select new ViewRptBill()
                                  {
                                      code = s.CODE,
                                      createDate = s.CREATE_DATE,
                                      staff = s.OPID,
                                      tableName = s.ORDER_COUPON.TABLE.TABLE_NAME,
                                      total = s.TOTAL_AMOUNT,
                                      note = s.NOTE,
                                      status = s.STATUS_CODE
                                  }).ToList<ViewRptBill>();
                }
                // hoa don khong su dung
                else if (billType == 3)
                {
                    resultList = (from s in CommonGlobalVariable.globalDataContext.BILLs
                                  where s.CREATE_DATE >= startDate && s.CREATE_DATE <= endDate
                                    && s.STATUS_CODE == (int)BillStatus.NOT_USE
                                  select new ViewRptBill()
                                  {
                                      code = s.CODE,
                                      createDate = s.CREATE_DATE,
                                      staff = s.OPID,
                                      tableName = s.ORDER_COUPON.TABLE.TABLE_NAME,
                                      total = s.TOTAL_AMOUNT,
                                      note = s.NOTE,
                                      status = s.STATUS_CODE
                                  }).ToList<ViewRptBill>();
                }
            }
            catch (Exception ex)
            {
                CommonLogging.fsWriteLog(CommonConstants.LOG_TYPE_ERROR,
                            "ReportCtrl.fsGetTotalBillList",
                            CommonUtils.fsParamListToString(new object[3] { startDate, endDate, billType}),
                            ex.ToString());
            }

            return resultList;
        }
    }
}
